/**
 * Description: NeoPoiUtil.java
 * Copyright (c) 2008-2009  Neo. 
 * All Rights Reserved.
 * @version 1.0  May 2, 2009 3:42:57 PM 李萌（neolimeng@gmail.com）created
 */
package org.paradise.dms.poi;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.builder.ReflectionToStringBuilder;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * Description: 
 * Copyright (c) 2008-2009  Neo. 
 * All Rights Reserved.
 * @version 1.0  May 2, 2009 3:42:57 PM 李萌（neolimeng@gmail.com）created
 */
public class NeoPoiUtil {
	private static Logger log = Logger.getLogger(PoiUtil.class);
	/**
	 * 
	 * Description:把生成的Excel文件写入制定的输出流中
	 * 
	 * @Version1.0 Apr 8, 2009 3:18:40 PM 李萌（neolimeng@gmail.com）创建
	 * @param workbook
	 *            To write the Excel a HSSFWorkbook is needed.
	 * @param os
	 *            os stands for the destination path of the Excel file.
	 */
	public static void poiWriteExcel(HSSFWorkbook workbook, OutputStream os) {
		try {
			workbook.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				os.flush();
				os.close();
				if (workbook != null) {
					workbook = null;
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 
	 * Description:
	 * Used to create an Excel file and write the file to the assigned OutputStream.
	 * titles:the titles of the Excel file.
	 * contentList:the contents, in which each Object[] stands for a row of data.
	 * os: the OutputStream, where the Excel file will be written. 
	 * @Version1.0 May 2, 2009 3:57:45 PM 李萌（neolimeng@gmail.com）创建
	 * @param titles
	 * @param contentList
	 * @param os
	 */
	@SuppressWarnings("unchecked")
	public static void poiWriteExcel(String[] titles,List contentList,OutputStream os){
		try{
			HSSFWorkbook workbook=new HSSFWorkbook();
			HSSFSheet sheet=workbook.createSheet();
			HSSFRow row=null;
			HSSFCell cell=null;
			
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.RED.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillBackgroundColor(HSSFColor.RED.index);
			
			//Create the titles of the Excel file.
			row=sheet.createRow(0);
			for (int i = 0; i < titles.length; i++) {
				cell = row.createCell(i);
				// cell.setCellStyle(titleStyle);
				cell.setCellValue(titles[i]);
			}
			// freeze the first row
			sheet.createFreezePane(0, 1);
			
			//Write the contents of to the Excel. The contents are contained in the contentList.
			for(int j=0;j<contentList.size();j++){//Rows
				//System.out.print("第"+j+"行数据：");
				row=sheet.createRow(j+1);
				Object[] contents=(Object[])contentList.get(j);
				//String tag = (String)contents[21]; 
				for(int k=0;k<contents.length;k++){//Each row.
					cell=row.createCell(k);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					/*
					if(null!= tag && "compare".equals(tag)) {
						cell.setCellStyle(cellStyle);
					}
					*/
		            //cell.setCellType(HSSFCell.ENCODING_UTF_16);
					if(k<contents.length){
						cell.setCellValue(contents[k] == null ? "":contents[k].toString());
						//System.out.print(contents[k].toString());
					}else{
						cell.setCellValue("");
						//System.out.println("写入空列！");
					}
				}
				//System.out.println();
			}
			
			//Write the Excel file into the OutputStream
			poiWriteExcel(workbook, os);
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Description: Use POI to read a Excel file.
	 * 
	 * The path, the start row, the start column number and the end column
	 * number are needed.
	 * 
	 * The method returns a List which contains many Object[], one of which
	 * stands for one row of data.
	 * 
	 * @Version1.0 Apr 23, 2009 1:42:54 AM 李萌（neolimeng@gmail.com）创建
	 * @param filePath
	 * @param startRow
	 * @param startColumn
	 * @param endColumn
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List poiReadExcel(String filePath, int sheetNumber, int startRow, int startColumn, int endColumn) {
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		FileInputStream fileIn = null;
		try {
			fileIn = new FileInputStream(filePath);
			fs = new POIFSFileSystem(fileIn);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(sheetNumber);
			
			// The list where correct results and failed results are saved, and
			// returned to the caller of this method.
			List list = new ArrayList();
			// The list used to save correct results
			List listCorrect = new ArrayList();
			// The list used to save failed results
			List listFailed = new ArrayList();
			
			row = sheet.getRow(startRow);
			int k = 0;// Counter the number of rows that has been read.
			while (row != null && row.getCell(0) != null
					&& row.getCell(0).getStringCellValue() != "") {
				Object[] obj = new Object[endColumn - startColumn + 1];
				try {
					for (int i = startColumn, j = 0; i <= endColumn; j++, i++) {
						cell = row.getCell(i);
						obj[j] = cell.getStringCellValue();
					}
					listCorrect.add(obj);
				} catch (Exception e) {
					listFailed.add(startRow+k);//Record the row number containing error data.
				}
				k++;
				row = sheet.getRow(startRow + k);
			}
			
			list.add(listCorrect);
			list.add(listFailed);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (null != fileIn) {
				try {
					fileIn.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * 
	 * Description: 读excel某一行，根据名称确定对应关系，返回对应关系的数组
	 * @Version1.0 Sep 18, 2009 2:46:19 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @return
	 */
	public static int[] poiReadFirstRowOfExcel(String excelFilePath, int sheetNumber, int startRow, int columnsNumsToRead){
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		FileInputStream fileIn = null;
		int [] orderarr = new int[columnsNumsToRead];
		try {
			// Read the Excel file and get the "sheetNumber" Excel sheet.
			fileIn = new FileInputStream(excelFilePath);
			fs = new POIFSFileSystem(fileIn);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(sheetNumber);

			// Read the "startRow" row of the Excel sheet.
			row = sheet.getRow(startRow);
			
			try {
				// Loop to read the data of one line.
				for (int i = 0; i < columnsNumsToRead; i++) {
					cell = row.getCell(i);
					if (null != cell) {
						String title = cell.getStringCellValue();
						if("学号".equals(title)){
							orderarr[i] = 0;
						}
						else if("姓名".equals(title)){
							orderarr[i] = 1;
						}
						else if("入学年份".equals(title)){
							orderarr[i] = 2;
						}
						else if("学制".equals(title)){
							orderarr[i] = 3;
						}
						else if("身份证".equals(title)){
							orderarr[i] = 4;
						}
						else if("学院".equals(title)){
							orderarr[i] = 5;
						}
						else if("专业代码".equals(title)){
							orderarr[i] = 6;
						}
						else if("班级".equals(title)){
							orderarr[i] = 7;
						}
						else if("性别".equals(title)){
							orderarr[i] = 8;
						}
						else if("出生日期".equals(title)){
							orderarr[i] = 9;
						}
						else if("民族".equals(title)){
							orderarr[i] = 10;
						}
						else if("政治面貌".equals(title)){
							orderarr[i] = 11;
						}
						else if("生源地".equals(title)){
							orderarr[i] = 12;
						}
						else if("毕业学校".equals(title)){
							orderarr[i] = 13;
						}
						else if("语种".equals(title)){
							orderarr[i] = 14;
						}
						else if("层次".equals(title)){
							orderarr[i] = 15;
						}
						else if("备注".equals(title)){
							orderarr[i] = 16;
						}
						else if("培养方式".equals(title)){
							orderarr[i] = 17;
						}
						else if("因材施教".equals(title)){
							orderarr[i] = 18;
						}
						else if("博导姓名".equals(title)){
							orderarr[i] = 19;
						}
						else if("毕业时间".equals(title)){
							orderarr[i] = 20;
						}
						else if("分流方向".equals(title)){
							orderarr[i] = 21;
						}else{
							orderarr[i] = -1;
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			
			return orderarr;
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (null != fileIn) {
				try {
					fileIn.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * 
	 * Description: Use POI to read an Excel file.
	 * @param excelFilePath
	 * @param sheetNumber
	 * @param startRow
	 * @param columnsToRead
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List poiReadExcel(String excelFilePath, int sheetNumber, int startRow, int[] columnsToRead) {
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		FileInputStream fileIn = null;
		try {
			// 读excel并根据 sheetNumber得到sheet
			fileIn = new FileInputStream(excelFilePath);
			fs = new POIFSFileSystem(fileIn);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(sheetNumber);

			// 方法的返回值,保存了正确值的集合和错误值的集合
			List list = new ArrayList();
			// 保存正确结果集的list
			List listCorrect = new ArrayList();
			// 保存错误结果集的list
			List listFailed = new ArrayList();

			//读startRow
			row = sheet.getRow(startRow);
			//从startRow开始累加
			int k = startRow;
			//obj表示一行数据
			while (null != row) {
				Object[] obj = new Object[columnsToRead.length];
				try {
					// 循环读每一行中的数据
					for (int i = 0; i < columnsToRead.length; i++) {
						
						if (columnsToRead[i] != -1) {
							cell = row.getCell(i);
							if (null != cell) {
								//obj[i] = cell.getStringCellValue();
								switch (cell.getCellType()) {
								case HSSFCell.CELL_TYPE_STRING:
									obj[columnsToRead[i]] = cell.getStringCellValue();
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									//因为excel中的学号单元格常常忘记设为文本类型
									java.text.DecimalFormat formatter = new java.text.DecimalFormat("########"); 
									String str = formatter.format(cell.getNumericCellValue()); 
									obj[columnsToRead[i]] = str;
									break;
								case HSSFCell.CELL_TYPE_BOOLEAN:
									obj[columnsToRead[i]]=cell.getBooleanCellValue();
									break;
								default:
								}
							}
						}
					}

					listCorrect.add(obj);
					k++;
				} catch (Exception e) {
					listFailed.add(k);// save the number of the row where the process of reading data fails.
					k++;// read next row.
					e.printStackTrace();
				}
				row = sheet.getRow(k);
			}
			list.add(listCorrect); 
			list.add(listFailed);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (null != fileIn) {
				try {
					fileIn.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
